#!/bin/bash

echo "🚀 一键解决PostgreSQL连接问题"
echo "============================="

# 设置颜色
RED='\033[0;31m'
GREEN='\033[0;32m'
YELLOW='\033[1;33m'
NC='\033[0m' # No Color

echo ""
echo -e "${YELLOW}步骤 1: 诊断当前问题${NC}"
echo "========================"

# 检查Docker容器
echo "检查PostgreSQL容器..."
POSTGRES_CONTAINERS=$(sudo docker ps -a | grep postgres)
if [ ! -z "$POSTGRES_CONTAINERS" ]; then
    echo -e "${GREEN}找到PostgreSQL容器:${NC}"
    echo "$POSTGRES_CONTAINERS"
else
    echo -e "${RED}没有找到PostgreSQL容器${NC}"
fi

echo ""
echo -e "${YELLOW}步骤 2: 清理并重新创建容器${NC}"
echo "================================"

# 停止所有PostgreSQL容器
echo "停止所有PostgreSQL相关容器..."
sudo docker ps | grep postgres | awk '{print $1}' | xargs -r sudo docker stop
sudo docker ps -a | grep postgres | awk '{print $1}' | xargs -r sudo docker rm

# 删除数据卷
echo "删除旧数据卷..."
sudo docker volume ls | grep postgres | awk '{print $2}' | xargs -r sudo docker volume rm

echo ""
echo -e "${YELLOW}步骤 3: 创建新的PostgreSQL容器${NC}"
echo "==================================="

# 创建新容器
echo "创建新的PostgreSQL容器..."
sudo docker run \
  --name postgres-aide-new \
  -e POSTGRES_PASSWORD=aide123456 \
  -e POSTGRES_DB=aide \
  -e POSTGRES_USER=postgres \
  -p 5432:5432 \
  -v postgres_aide_data:/var/lib/postgresql/data \
  -d postgres:latest

# 等待启动
echo "等待容器启动..."
sleep 15

# 检查容器状态
echo "检查容器状态..."
sudo docker ps | grep postgres-aide-new

echo ""
echo -e "${YELLOW}步骤 4: 配置环境变量${NC}"
echo "========================"

# 进入后端目录
cd /home/ubuntu/aide/aide/backend

# 创建.env文件
echo "创建.env配置文件..."
cat > .env << EOF
# 数据库配置
DATABASE_URL=postgresql://postgres:aide123456@localhost:5432/aide
DB_HOST=localhost
DB_PORT=5432
DB_NAME=aide
DB_USER=postgres
DB_PASSWORD=aide123456

# JWT配置
JWT_SECRET=aide_jwt_secret_key_change_in_production_$(date +%s)
JWT_EXPIRES_IN=7d

# 服务器配置
PORT=5000
NODE_ENV=development

# OpenAI配置 (可选)
# OPENAI_API_KEY=your_openai_api_key_here
EOF

echo -e "${GREEN}✅ .env文件已创建${NC}"

# 设置环境变量
export DATABASE_URL="postgresql://postgres:aide123456@localhost:5432/aide"
export JWT_SECRET="aide_jwt_secret_key_change_in_production_$(date +%s)"

echo ""
echo -e "${YELLOW}步骤 5: 初始化数据库${NC}"
echo "======================"

# 等待数据库完全启动
echo "等待PostgreSQL完全启动..."
sleep 10

# 测试连接
echo "测试数据库连接..."
PGPASSWORD=aide123456 psql -h localhost -p 5432 -U postgres -d postgres -c "SELECT version();" 2>/dev/null
if [ $? -eq 0 ]; then
    echo -e "${GREEN}✅ 数据库连接成功${NC}"
    
    # 运行数据库初始化
    echo "运行数据库初始化..."
    if [ -f "init-database-fixed.js" ]; then
        node init-database-fixed.js
    elif [ -f "init-database.js" ]; then
        node init-database.js
    else
        echo -e "${RED}❌ 找不到数据库初始化脚本${NC}"
    fi
else
    echo -e "${RED}❌ 数据库连接失败，请检查容器状态${NC}"
fi

echo ""
echo -e "${YELLOW}步骤 6: 验证修复结果${NC}"
echo "======================"

# 创建测试脚本
cat > test-backend-connection.js << 'EOF'
const { Client } = require('pg');
require('dotenv').config();

async function testBackend() {
  console.log('🧪 测试后端数据库连接...');
  
  const client = new Client({
    connectionString: process.env.DATABASE_URL
  });

  try {
    await client.connect();
    console.log('✅ 连接成功');
    
    // 测试查询
    const result = await client.query('SELECT NOW()');
    console.log('✅ 查询测试成功:', result.rows[0].now);
    
    // 检查表是否存在
    const tables = await client.query(`
      SELECT table_name 
      FROM information_schema.tables 
      WHERE table_schema = 'public'
    `);
    
    console.log('📋 数据库表:');
    tables.rows.forEach(row => {
      console.log('  -', row.table_name);
    });
    
  } catch (error) {
    console.error('❌ 测试失败:', error.message);
  } finally {
    await client.end();
  }
}

testBackend();
EOF

# 运行测试
echo "运行连接测试..."
node test-backend-connection.js

echo ""
echo -e "${GREEN}🎉 修复完成！${NC}"
echo "==============="
echo ""
echo "现在可以启动后端服务:"
echo -e "${YELLOW}cd /home/ubuntu/aide/aide/backend${NC}"
echo -e "${YELLOW}npm run dev${NC}"
echo ""
echo "如果仍有问题，请检查:"
echo "1. 容器状态: sudo docker ps | grep postgres"
echo "2. 容器日志: sudo docker logs postgres-aide-new"
echo "3. 环境变量: cat .env"

# 清理临时文件
rm -f test-backend-connection.js 2>/dev/null
